-- --------------------------------------
-- Procedure structure for "account"
-- --------------------------------------

-- init account -------------------------
-- 创建表
DROP TABLE IF EXISTS "account" CASCADE;  
CREATE TABLE IF NOT EXISTS "account" (
    "id" BIGSERIAL NOT NULL,
    "name" VARCHAR(60) NOT NULL UNIQUE,
    "pwd" VARCHAR(50) NOT NULL,
    "guid" UUID DEFAULT uuid_generate_v4() UNIQUE,
    "players" UUID[] NOT NULL DEFAULT ARRAY[]::UUID[],
    "exp" BIGINT NOT NULL DEFAULT '0',
    "gold" BIGINT NOT NULL DEFAULT '0',
    "privilege" SMALLINT NOT NULL DEFAULT '0',
    "createtime" TIMESTAMP(6) DEFAULT NOW(),
    PRIMARY KEY (id)
);
-- 表属性
SELECT setval('account_id_seq', 10000000);
-- 表说明
COMMENT ON TABLE account IS '账户信息表';
-- 字段说明
COMMENT ON COLUMN account.id IS '账户ID';
COMMENT ON COLUMN account.name IS '账户名称';
COMMENT ON COLUMN account.pwd IS '账户密码';
COMMENT ON COLUMN account.guid IS '账户GUID';
COMMENT ON COLUMN account.players IS '账户玩家列表';
COMMENT ON COLUMN account.exp IS '账户经验';
COMMENT ON COLUMN account.gold IS '账户金币';
COMMENT ON COLUMN account.privilege IS '账户权限';
COMMENT ON COLUMN account.createtime IS '账户创建时间';
-- init account -------------------------


-- account function ---------------------------
-- 添加账号
CREATE OR REPLACE FUNCTION "account_insert"(IN acc_name VARCHAR(60), IN acc_pwd VARCHAR(50))
RETURNS BIGINT AS $$
DECLARE
    acc_id BIGINT;
    plr_guid UUID;
BEGIN
    -- 添加player记录
    SELECT player_insert(acc_name) INTO plr_guid;
    IF plr_guid IS NULL THEN
        RETURN NULL;
    END IF;

    -- 绑定到新创建的账户
    INSERT INTO account (name, pwd, players, createtime) 
        VALUES (acc_name, acc_pwd, ARRAY[plr_guid]::UUID[], NOW()) RETURNING id INTO acc_id;
    RETURN acc_id;
END;
$$ 
LANGUAGE plpgsql;

-- 用id查询账号
CREATE OR REPLACE FUNCTION "account_select"(IN acc_id BIGINT)
RETURNS account AS $$
DECLARE
    result account;
BEGIN
    SELECT * INTO result FROM account WHERE id = acc_id;
    PERFORM account_merge_to_cache(result);
    RETURN result;
END;
$$ 
LANGUAGE plpgsql;

-- 用GUID查询账号
CREATE OR REPLACE FUNCTION "account_select_by_guid"(IN acc_guid UUID)
RETURNS account AS $$
DECLARE
    result account;
BEGIN
    SELECT * INTO result FROM account WHERE guid = acc_guid;
    PERFORM account_merge_to_cache(result);
    RETURN result;
END;
$$ 
LANGUAGE plpgsql;

-- 用名字查询账号
CREATE OR REPLACE FUNCTION "account_select_by_name"(IN acc_name name)
RETURNS account AS $$
DECLARE
    result account;
BEGIN
    SELECT * INTO result FROM account WHERE name = acc_name;
    PERFORM account_merge_to_cache(result);
    RETURN result;
END;
$$ 
LANGUAGE plpgsql;

-- 合并account结果到cache
CREATE OR REPLACE FUNCTION "account_merge_to_cache"(IN tab account)
RETURNS INTEGER AS $$
DECLARE
    acc_key_id text;
    acc_key_name text;
    acc_key_guid text;
    tmp_guid text;
    tmp_players text;
    acc_value text[];
BEGIN
    IF tab IS NULL THEN
        RETURN NULL;
    END IF;

    SELECT regexp_replace(''||tab.guid, '-','','g') INTO tmp_guid;
    SELECT array_to_string(tab.players, ',') INTO tmp_players;
    SELECT regexp_replace(''||tmp_players, '-','','g') INTO tmp_players;
    
    acc_key_id = 'account:'||tab.id;
    acc_key_name = 'account:name_'||tab.name;
    acc_key_guid = 'account:guid_'||tmp_guid;
    acc_value = '{
        id,         '||tab.id||',
        name,       '||tab.name||',
        pwd,        '||tab.pwd||',
        guid,       '||tmp_guid||',
        players,    '||tmp_players||',
        exp,        '||tab.exp||',
        gold,       '||tab.gold||',
        privilege,  '||tab.privilege||',
        createtime, '||tab.createtime||'
    }';

    LOOP
        UPDATE account_cache
            SET val = acc_value
            WHERE key = acc_key_id OR key = acc_key_name OR key = acc_key_guid;
        IF found THEN
            RETURN NULL;
        END IF;

        BEGIN
            INSERT INTO account_cache(key, val) VALUES (acc_key_id, acc_value);
            INSERT INTO account_cache(key, val) VALUES (acc_key_name, acc_value);
            INSERT INTO account_cache(key, val) VALUES (acc_key_guid, acc_value);
            RETURN NULL;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$ 
LANGUAGE plpgsql;
-- account function ---------------------------


-- account cache -------------------------
DROP FOREIGN TABLE IF EXISTS "account_cache" CASCADE;
CREATE FOREIGN TABLE "account_cache" (key text, val text[])
	SERVER redis_server
	OPTIONS (database '0', tabletype 'hash', tablekeyprefix 'account:');
-- account cache -------------------------


-- trigger function -------------------------
CREATE OR REPLACE FUNCTION "account_cache_sync"()
RETURNS TRIGGER AS $$
BEGIN
    RETURN (SELECT account_merge_to_cache(NEW));
END;
$$
LANGUAGE plpgsql;
-- trigger function -------------------------


-- trigger -------------------------
DROP TRIGGER IF EXISTS account_cache_update ON account CASCADE;
CREATE TRIGGER account_cache_update
	AFTER UPDATE ON account
	FOR EACH ROW EXECUTE PROCEDURE account_cache_sync();
-- trigger -------------------------

-- GM account ---------------------------
DO $$
DECLARE 
    tmp_guid UUID;
BEGIN
    SELECT player_insert('Aliner') INTO tmp_guid;
    INSERT INTO account (id, name, players, pwd) 
    VALUES (1, 'Aliner', ARRAY[tmp_guid]::UUID[], '11111') 
    ON conflict(id)
    DO NOTHING;
END $$;
-- GM account ---------------------------

-- unit test ---------------------------
SELECT account_insert('大老婆', '1111111');
SELECT account_select(1);
-- unit test ---------------------------
